// Copyright 2019 chunbao Author. All Rights Reserved.
//
//免费使用，出现任何问题均与本人无关
//
package db

import (
	"encoding/json"
	"reflect"
	"strconv"
	"strings"
)

type SqlMysqlBuilder struct {
	builStrMap map[string]interface{}
}

//初始化数据
func (s *SqlMysqlBuilder) initdb() GetBuilder {
	s.builStrMap = make(map[string]interface{})
	s.builStrMap["name"] = ""
	s.builStrMap["where"] = []string{}
	s.builStrMap["whereOr"] = []string{}
	s.builStrMap["whereIn"] = []string{}
	s.builStrMap["whereNotIn"] = []string{}
	s.builStrMap["field"] = []string{}
	s.builStrMap["order"] = []string{}
	s.builStrMap["join"] = []string{}
	s.builStrMap["page"] = 1
	s.builStrMap["limit"] = 10
	s.builStrMap["limitSql"] = ""
	s.builStrMap["sql"] = ""
	s.builStrMap["param"] = []orm.Params{}
	return s
}

//设置表名称
func (s *SqlMysqlBuilder) Name(table string) GetBuilder {
	s.initdb()
	s.builStrMap["name"] = table
	return s
}

//设置where and 条件
func (s *SqlMysqlBuilder) Where(cond string) GetBuilder {
	s.builStrMap["where"] = append(s.builStrMap["where"].([]string), cond)
	return s
}

//设置where or 条件
func (s *SqlMysqlBuilder) WhereOr(cond string) GetBuilder {
	s.builStrMap["whereOr"] = append(s.builStrMap["whereOr"].([]string), cond)
	return s
}

//agr 1,agr 2 []string
func (s *SqlMysqlBuilder) WhereIn(vars ...interface{}) GetBuilder {
	var cond string
	if len(vars) == 1 && reflect.TypeOf(vars[0]).String() == "string" {
		cond = vars[0].(string)
	} else if len(vars) == 2 && reflect.TypeOf(vars[0]).String() == "string" && reflect.TypeOf(vars[1]).String() == "[]string" {
		instr := strings.Join(vars[1].([]string), ", ")
		cond = vars[0].(string) + " IN " + "(" + instr + ")"
	} else {
		return s
	}
	s.builStrMap["whereIn"] = append(s.builStrMap["whereIn"].([]string), cond)
	return s
}

func (s *SqlMysqlBuilder) WhereNotIn(vars ...interface{}) GetBuilder {
	var cond string
	if len(vars) == 1 && reflect.TypeOf(vars[0]).String() == "string" {
		cond = vars[0].(string)
	} else if len(vars) == 2 && reflect.TypeOf(vars[0]).String() == "string" && reflect.TypeOf(vars[1]).String() == "[]string" {
		instr := strings.Join(vars[1].([]string), ", ")
		cond = vars[0].(string) + " IN " + "(" + instr + ")"
	} else {
		return s
	}
	s.builStrMap["whereNotIn"] = append(s.builStrMap["whereNotIn"].([]string), cond)
	return s
}

//设置字段
func (s *SqlMysqlBuilder) Field(fields ...string) GetBuilder {
	s.builStrMap["field"] = append(s.builStrMap["field"].([]string), strings.Join(fields, ", "))
	return s
}

//排序
func (s *SqlMysqlBuilder) Order(field string, by string) GetBuilder {
	s.builStrMap["order"] = append(s.builStrMap["order"].([]string), "`"+field+"` "+strings.ToUpper(by))
	return s
}

//连接
func (s *SqlMysqlBuilder) Join(table string, on string, cond string) GetBuilder {
	cond = strings.ToUpper(cond)
	if cond != "INNER" && cond != "LEFT" && cond != "RIGHT" {
		return s
	}
	s.builStrMap["join"] = append(s.builStrMap["join"].([]string), cond+" JOIN "+table+" ON "+on)
	return s
}

//分页
func (s *SqlMysqlBuilder) Page(page int64, limit int64) string {
	s.builStrMap["page"] = page
	s.builStrMap["limit"] = limit
	off := (page - 1) * limit
	s.builStrMap["limitSql"] = " LIMIT " + strconv.FormatInt(off, 10) + ", " + strconv.FormatInt(limit, 10)
	s.builStrMap["sql"] = s.Select() + s.builStrMap["limitSql"].(string)
	return s.builStrMap["sql"].(string)
}

//统计
func (s *SqlMysqlBuilder) Count() string {
	sql := "SELECT "
	field := "count(*) AS count"
	sql += field + " FROM " + s.builStrMap["name"].(string) + " "
	join := s.joinStr()
	if join != "" {
		sql += " " + join
	}
	wh := s.whereStr()
	if wh != "" {
		sql += " WHERE " + wh
	}
	s.builStrMap["sql"] = sql
	return s.builStrMap["sql"].(string)
}

//查询列表
func (s *SqlMysqlBuilder) Select() string {
	// select * from table
	//join
	//where,whereIn,whereNotIn,whereOr
	//order
	//limit
	sql := "SELECT "
	field := strings.Join(s.builStrMap["field"].([]string), ", ")
	if field == "" {
		field = "*"
	}
	sql += field + " FROM " + s.builStrMap["name"].(string)
	join := s.joinStr()
	if join != "" {
		sql += " " + join
	}
	wh := s.whereStr()
	if wh != "" {
		sql += " WHERE " + wh
	}
	order := strings.Join(s.builStrMap["order"].([]string), ", ")
	if order != "" {
		sql += " ORDER BY " + order
	}
	s.builStrMap["sql"] = sql
	return s.builStrMap["sql"].(string)
}

//查询一条
func (s *SqlMysqlBuilder) Find() string {
	s.builStrMap["sql"] = s.Select() + " LIMIT 1"
	return s.builStrMap["sql"].(string)
}

//插入
func (s *SqlMysqlBuilder) Insert(maps map[string]interface{}) string {
	if len(maps) == 0 {
		return ""
	}
	sql := "INSERT INTO `" + s.builStrMap["name"].(string) + "` "
	fild := []string{}
	value := []string{}
	for key, val := range maps {
		fild = append(fild, "`"+key+"`")
		if reflect.TypeOf(val).String() == "string" {
			value = append(value, "`"+val.(string)+"`")
		} else {
			b, _ := json.Marshal(val)
			value = append(value, string(b[:]))
		}
	}
	sql += "(" + strings.Join(fild, ", ") + ") VALUES (" + strings.Join(value, ", ") + ")"
	if len(maps) == 0 {
		sql = ""
	}
	s.builStrMap["sql"] = sql
	return s.builStrMap["sql"].(string)
}

//更新
func (s *SqlMysqlBuilder) Update(maps map[string]interface{}) string {
	wh := s.whereStr()
	if len(maps) == 0 || wh == "" {
		return ""
	}
	sql := "UPDATE `" + s.builStrMap["name"].(string) + "` SET "
	value := []string{}
	for key, val := range maps {
		fild := "`" + key + "`="
		if reflect.TypeOf(val).String() == "string" {
			value = append(value, fild+"`"+val.(string)+"`")
		} else {
			b, _ := json.Marshal(val)
			value = append(value, fild+string(b[:]))
		}
	}
	sql += strings.Join(value, ", ") + " WHERE " + wh
	if len(maps) == 0 {
		sql = ""
	}
	s.builStrMap["sql"] = sql
	return s.builStrMap["sql"].(string)
}

//删除
func (s *SqlMysqlBuilder) Delete() string {
	wh := s.whereStr()
	if wh == "" {
		return ""
	}
	sql := "DELETE FROM `" + s.builStrMap["name"].(string) + " WHERE " + wh
	return sql
}

//返回查询结果map
func (s *SqlMysqlBuilder) QueryMap() (orm.Params, error) {
	var list []orm.Params
	o := orm.NewOrm()
	_, err := o.Raw(s.builStrMap["sql"].(string), s.builStrMap["param"]).Values(&list)
	return list[0], err
}

/*****************************************/
//以下是私有方法
//获取join字符串
func (s *SqlMysqlBuilder) joinStr() string {
	return strings.Join(s.builStrMap["join"].([]string), " ")
}

//获取where字符串
func (s *SqlMysqlBuilder) whereStr() string {
	whstr := []string{}
	where := strings.Join(s.builStrMap["where"].([]string), " AND ")
	whereIn := strings.Join(s.builStrMap["whereIn"].([]string), " AND ")
	whereNotIn := strings.Join(s.builStrMap["whereNotIn"].([]string), " AND ")
	whereOr := strings.Join(s.builStrMap["whereOr"].([]string), " OR ")
	if where != "" {
		whstr = append(whstr, where)
	}
	if whereIn != "" {
		whstr = append(whstr, whereIn)
	}
	if whereNotIn != "" {
		whstr = append(whstr, whereNotIn)
	}
	wh := strings.Join(whstr, " AND ")
	if wh != "" && whereOr != "" {
		wh += " OR " + whereOr
	} else {
		if whereOr != "" {
			wh = whereOr
		}
	}
	return wh
}